/**
 * 
 */
package dt.inlee.system.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import dt.inlee.common.dao.impl.BaseDao;
import dt.inlee.common.exception.FrameworkException;
import dt.inlee.common.util.BaseUtil;
import dt.inlee.domain.SysMenu;
import dt.inlee.domain.SysOrganization;
import dt.inlee.domain.SysRole;
import dt.inlee.domain.SysUser;

/**
 * @功能说明：系统人员数据访问层
 * @作者： 印鲜刚
 * @创建日期： 2010-5-6 @
 */
@SuppressWarnings("rawtypes")
@Repository("sysUserDao")
public class SysUserDao extends BaseDao {
	private static Logger logger = Logger.getLogger(SysUserDao.class);

	/**
	 * 通过角色代码获取拥有该角色的用户
	 * 
	 * @param code
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
    public List<SysUser> findSysUserBySysRoleCode(String code)
			throws FrameworkException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT DISTINCT su FROM SysUser su LEFT JOIN su.sysRoles sr WHERE sr.code=?");
		return hibernateTemplate.find(sb.toString(), code);

	}

	/**
	 * 根据条件wyId查询用户
	 * 
	 * @param name
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysUser> getListSysUser(String wyId)
			throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		criteria.add(Restrictions.eq("wyId", wyId));
		return criteria.list();
	}

	/**
	 * 系统登录功能
	 * 
	 * @param userName
	 * @param password
	 * @return
	 * @throws FrameworkException
	 */
    public Map<String, SysUser> login(String userName, String password)
			throws FrameworkException {
		Map<String, SysUser> sysUsers = null;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT su,CASE WHEN (su.effectStartDate is null AND su.effectEndDate is null) THEN ('sucess') ");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is null AND CURRENT_DATE()>= su.effectStartDate) THEN ('startSuccess')");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is null AND su.effectStartDate > CURRENT_DATE()) THEN ('startError')");
		sb.append("WHEN (su.effectStartDate is null AND su.effectEndDate is not null AND su.effectEndDate>=CURRENT_DATE()) THEN ('endSuccess') ");
		sb.append("WHEN (su.effectStartDate is null AND su.effectEndDate is not null AND CURRENT_DATE()> su.effectEndDate) THEN ('endError') ");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is not null AND CURRENT_DATE()>=su.effectStartDate AND su.effectEndDate>=CURRENT_DATE()) THEN ('betweenSuccess') ");
		sb.append("ELSE ('betweenError') END ");
		sb.append("FROM SysUser su WHERE su.disabled = 1 AND su.isOperator = 1 AND su.userName = ? AND su.password = ? ");

		List loginObject = this.hibernateTemplate.find(sb.toString(),
				new Object[] { userName, password });
		if (!BaseUtil.isEmpty(loginObject) && loginObject.size() > 0) {
			sysUsers = new HashMap<String, SysUser>();
			Object[] objs = (Object[]) loginObject.get(0);
			sysUsers.put(objs[1].toString(), (SysUser) objs[0]);
			return sysUsers;

		}
		return null;
	}

	/**
	 * ********SSO Login*******************************************
	 * 
	 * @param userName
	 * @param password
	 * @return
	 * @throws FrameworkException
	 */
	public Map<String, SysUser> loginSSO(String userName)
			throws FrameworkException {
		Map<String, SysUser> sysUsers = null;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT su,CASE WHEN (su.effectStartDate is null AND su.effectEndDate is null) THEN ('sucess') ");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is null AND CURRENT_DATE()>= su.effectStartDate) THEN ('startSuccess')");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is null AND su.effectStartDate > CURRENT_DATE()) THEN ('startError')");
		sb.append("WHEN (su.effectStartDate is null AND su.effectEndDate is not null AND su.effectEndDate>=CURRENT_DATE()) THEN ('endSuccess') ");
		sb.append("WHEN (su.effectStartDate is null AND su.effectEndDate is not null AND CURRENT_DATE()> su.effectEndDate) THEN ('endError') ");
		sb.append("WHEN (su.effectStartDate is not null AND su.effectEndDate is not null AND CURRENT_DATE()>=su.effectStartDate AND su.effectEndDate>=CURRENT_DATE()) THEN ('betweenSuccess') ");
		sb.append("ELSE ('betweenError') END ");
		sb.append("FROM SysUser su WHERE su.disabled = 1 AND su.isOperator = 1 AND su.userName = ? ");

		List loginObject = this.hibernateTemplate.find(sb.toString(),
				new Object[] { true, true, userName });
		if (!BaseUtil.isEmpty(loginObject) && loginObject.size() > 0) {
			sysUsers = new HashMap<String, SysUser>();
			Object[] objs = (Object[]) loginObject.get(0);
			sysUsers.put(objs[1].toString(), (SysUser) objs[0]);
			return sysUsers;

		}
		return null;
	}

	/**
	 * 根据条件查询人员
	 * 
	 * @param params
	 * @param currentPage
	 * @param pageSize
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public List<SysUser> findAllSysUsers(Map<String, Object> params,
			int currentPage, int pageSize) throws FrameworkException {
		StringBuffer hql = new StringBuffer();
		if (!BaseUtil.isEmpty(params, "roleName")) {
			params.put("roleName", "%" + params.get("roleName") + "%");
			hql.append("SELECT DISTINCT su FROM SysUser su LEFT JOIN su.sysRoles sr where (1 = 1 ");
			hql.append("AND sr.name like :roleName ");
		} else {
			hql.append("SELECT DISTINCT su FROM SysUser su WHERE (1 = 1 ");
		}
		if (!BaseUtil.isEmpty(params, "realName")) {
			params.put("realName", "%" + params.get("realName") + "%");
			hql.append("AND su.realName like :realName ");
		}
		if (!BaseUtil.isEmpty(params, "gender"))
			hql.append("AND su.gender = :gender ");
		if (!BaseUtil.isEmpty(params, "userName")) {
			params.put("userName", "%" + params.get("userName") + "%");
			hql.append("AND su.userName like :userName ");
		}
		if (!BaseUtil.isEmpty(params, "workYears"))
			hql.append("AND su.workYears = :workYears ");
		if (!BaseUtil.isEmpty(params, "birthDay"))
			hql.append("AND su.birthDay = :birthDay ");
		if (!BaseUtil.isEmpty(params, "staffKind"))
			hql.append("AND su.staffKind = :staffKind ");
		if (!BaseUtil.isEmpty(params, "effectStartDate"))
			hql.append("AND su.effectStartDate >= :effectStartDate ");
		if (!BaseUtil.isEmpty(params, "effectEndDate"))
			hql.append("AND su.effectEndDate <= :effectEndDate ");
		if (!BaseUtil.isEmpty(params, "disabled"))
			hql.append("AND su.disabled = :disabled ");
		if (!BaseUtil.isEmpty(params, "isOperator"))
			hql.append("AND su.isOperator = :isOperator ");
		if (!BaseUtil.isEmpty(params, "identityCard")) {
			params.put("identityCard", "%" + params.get("identityCard") + "%");
			hql.append("AND su.identityCard like :identityCard ");
		}
		if (!BaseUtil.isEmpty(params, "wyId")) {
			hql.append("AND su.wyId = :wyId ");
		}
		hql.append(") ");
		hql.append(" order by su.id desc");
		return this.findAllEntitiesByCondition(hql.toString(), params,
				currentPage, pageSize);

	}

	/**
	 * 根据条件统计人员总数
	 * 
	 * @param params
	 * @return
	 */
	public int getSysUserTotalRows(Map<String, Object> params)
			throws FrameworkException {
		StringBuffer hql = new StringBuffer();
		if (!BaseUtil.isEmpty(params, "roleName")) {
			params.put("roleName", "%" + params.get("roleName") + "%");
			hql.append("SELECT COUNT(*) FROM SysUser su LEFT JOIN su.sysRoles sr where (1 = 1 ");
			hql.append("AND sr.name like :roleName ");
		} else {
			hql.append("SELECT COUNT(*) FROM SysUser su WHERE (1 = 1 ");
		}
		if (!BaseUtil.isEmpty(params, "realName")) {
			params.put("realName", "%" + params.get("realName") + "%");
			hql.append("AND su.realName like :realName ");
		}
		if (!BaseUtil.isEmpty(params, "gender"))
			hql.append("AND su.gender = :gender ");
		if (!BaseUtil.isEmpty(params, "userName")) {
			params.put("userName", "%" + params.get("userName") + "%");
			hql.append("AND su.userName like :userName ");
		}
		if (!BaseUtil.isEmpty(params, "workYears"))
			hql.append("AND su.workYears = :workYears ");
		if (!BaseUtil.isEmpty(params, "birthDay"))
			hql.append("AND su.birthDay = :birthDay ");
		if (!BaseUtil.isEmpty(params, "staffKind"))
			hql.append("AND su.staffKind = :staffKind ");
		if (!BaseUtil.isEmpty(params, "effectStartDate"))
			hql.append("AND su.effectStartDate >= :effectStartDate ");
		if (!BaseUtil.isEmpty(params, "effectEndDate"))
			hql.append("AND su.effectEndDate <= :effectEndDate ");
		if (!BaseUtil.isEmpty(params, "disabled"))
			hql.append("AND su.disabled = :disabled ");
		if (!BaseUtil.isEmpty(params, "isOperator"))
			hql.append("AND su.isOperator = :isOperator ");
		if (!BaseUtil.isEmpty(params, "identityCard")) {
			params.put("identityCard", "%" + params.get("identityCard") + "%");
			hql.append("AND su.identityCard like :identityCard ");
		}
		if (!BaseUtil.isEmpty(params, "wyId")) {
			if (params.get("wyId").toString().equals("0")) {
				params.remove("wyId");
			} else {
				hql.append("AND su.wyId = :wyId ");
			}
		}
		if (!BaseUtil.isEmpty(params, "deptId")) {
			if (params.get("deptId").toString().equals("0")) {
				params.remove("deptId");
			} else {
				hql.append("AND su.deptId = :deptId ");
			}
		}
		hql.append(") ");
		return this.getTotalRows(hql.toString(), params);

	}

	/**
	 * 通过组织机构ID查询未与该组织机构关联的人员总数
	 * 
	 * @param orgId
	 * 
	 * @param params
	 * @return
	 */
	public int getNotSelectedSysUserTotalRows(String orgId,
			Map<String, Object> params) throws FrameworkException {
		StringBuffer hql = new StringBuffer();
		hql.append("SELECT COUNT(*) FROM SysUser su WHERE su.disabled = :disabled AND su.isOperator = :isOperator AND su.id not in (SELECT suu FROM SysUser suu LEFT JOIN suu.sysOrganizations so where ");
		hql.append("so.id = :sysOrganizationId) ");
		params.put("sysOrganizationId", orgId);
		params.put("disabled", true);
		params.put("isOperator", true);
		if (!BaseUtil.isEmpty(params)) {
			if (!BaseUtil.isEmpty(params, "realName")) {
				params.put("realName", "%" + params.get("realName") + "%");
				hql.append("AND su.realName like :realName ");
			}
			if (!BaseUtil.isEmpty(params, "identityCard")) {
				params.put("identityCard", "%" + params.get("identityCard")
						+ "%");
				hql.append("AND su.identityCard like :identityCard ");
			}
			logger.debug("getNotSelectedSysUserTotalRows:hql=" + hql.toString());
		}
		return this.getTotalRows(hql.toString(), params);
	}

	/**
	 * 通过组织机构ID查询未与该组织机构关联的人员
	 * 
	 * @param orgId
	 * 
	 * @param params
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysUser> findNotSelectedSysUsers(String orgId,
			Map<String, Object> params, int currentPage, int pageSize)
			throws FrameworkException {
		StringBuffer hql = new StringBuffer();
		hql.append("SELECT DISTINCT su FROM SysUser su WHERE su.disabled = :disabled AND su.isOperator = :isOperator AND su.id not in (SELECT suu FROM SysUser suu LEFT JOIN suu.sysOrganizations so where ");
		hql.append("so.id = :sysOrganizationId) ");
		params.put("sysOrganizationId", orgId);
		params.put("disabled", true);
		params.put("isOperator", true);
		if (!BaseUtil.isEmpty(params)) {
			if (!BaseUtil.isEmpty(params, "realName")) {
				params.put("realName", "%" + params.get("realName") + "%");
				hql.append("AND su.realName like :realName ");
			}
			if (!BaseUtil.isEmpty(params, "identityCard")) {
				params.put("identityCard", "%" + params.get("identityCard")
						+ "%");
				hql.append("AND su.identityCard like :identityCard ");
			}
			logger.debug("findNotSelectedSysUsers:hql=" + hql.toString());
		}
		return this.findAllEntitiesByCondition(hql.toString(), params,
				currentPage, pageSize);
	}

	/**
	 * 根据用户ID获取该用户
	 * 
	 * @param id
	 * @return
	 */
	public SysUser getUserById(int id) {
		SysUser user = (SysUser) this.hibernateTemplate.get(SysUser.class, id);
		return user;
	}

	/**
	 * 根据用户id 获取该用户的权限菜单
	 * 
	 * @param id
	 * @return
	 * @throws FrameworkException
	 */
	public List<SysMenu> queryRightsMenus(int id) throws FrameworkException {
		SysUser sysUser = getUserById(id);
		List<SysRole> roles = sysUser.getSysRoles();
		// 存放用户权限菜单
		Set<SysMenu> menus = new HashSet<SysMenu>();
		for (SysRole sysRole : roles) {
			List<SysMenu> tempMenus = sysRole.getSysMenus();
			for (SysMenu sysMenu : tempMenus) {
				menus.add(sysMenu);
			}
		}
		List<SysMenu> list = new ArrayList<SysMenu>();
		Iterator<SysMenu> Imenus = menus.iterator();
		while (Imenus.hasNext()) {
			SysMenu sysMenu = Imenus.next();
			list.add(sysMenu);
		}
		return list;
	}

	/**
	 * 移除用户与组织机构,角色的映射关系
	 * 
	 * @param sysUserId
	 * @throws FrameworkException
	 */
	public void removeSysOrgAndSysRole(String sysUserId)
			throws FrameworkException {
		this.jdbcTemplate
				.execute("delete from KQ_SYSROLE_KQ_OPERATOR where sysusers_OpID='"
						+ sysUserId + "'");
		this.jdbcTemplate
				.execute("delete from KQ_OPERATOR_KQ_SYSORGANIZATION where sysusers_OpID='"
						+ sysUserId + "'");
	}

	/**
	 * 通过用户名查找用户
	 * 
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findSysUserByName(String userName) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(userName)) {
			criteria.add(Restrictions.eq("userName", userName));
		}
		criteria.add(Restrictions.eq("disabled", true));
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}

	/**
	 * 通过登录账号与企业ID
	 * 
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public boolean checkOperator(String loginID, int wyId,
			String filterEmployeeID) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(loginID)) {
			criteria.add(Restrictions.eq("userName", loginID));
		}
		if (!BaseUtil.isEmpty(wyId) && wyId != 0) {
			criteria.add(Restrictions.eq("wyId", wyId));
		}
		if (!BaseUtil.isEmpty(filterEmployeeID)) {
			criteria.add(Restrictions.ne("employeeID", filterEmployeeID));
		}
		criteria.add(Restrictions.eq("disabled", true));
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return true;
		return false;
	}

	/**
	 * 通过MED用户ID查找登录账号
	 * 
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findSysUserByMedUserID(String medUserID)
			throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(medUserID)) {
			criteria.add(Restrictions.eq("med_UserID", medUserID));
		}
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}

	/**
	 * 通过员工编号查找登录账号
	 * 
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findByEmployeeID(int employeeID) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(employeeID)) {
			criteria.add(Restrictions.eq("employeeID", employeeID));
		}
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}

	/**
	 * 通过用户ID及用户名查找用户
	 * 
	 * @param id
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findSysUserByIdAndName(int id, String userName)
			throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(userName)) {
			criteria.add(Restrictions.eq("userName", userName));
		}
		criteria.add(Restrictions.eq("disabled", true));
		criteria.add(Restrictions.ne("id", id));
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}

	/**
	 * 通过loginid@key查找用户
	 * 
	 * @param id
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findByLoginKey(String loginKey) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(loginKey)) {
			criteria.add(Restrictions.eq("loginKey", loginKey));
		}
		criteria.add(Restrictions.eq("disabled", true));

		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}

	/**
	 * 根据用户id 获取该用户的权限菜单,用于单点登录CAS
	 * 
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysMenu> queryRightsMenusForSSO(String userId) {
		// 此处需打开Session
		Session s = hibernateTemplate.getSessionFactory().openSession();
		// 使用SQL查找菜单ID
		SQLQuery sqlQuery = s
				.createSQLQuery("select sysMenus_id from KQ_SYSMENU_KQ_SYSROLE where sysRoles_id in ("
						+ "select sysRoles_id from  KQ_SYSROLE_KQ_OPERATOR where sysUsers_OpID = :userId)");
		sqlQuery.setParameter("userId", userId);
		List<String> menuIds = sqlQuery.list();
		// 使用HQL返回菜单列表
		Query query = s.createQuery("from SysMenu sm where sm.id in (:mId) ");
		if (menuIds.size() != 0) {
			query.setParameterList("mId", menuIds);
			s.clear();
			return query.list();
		}
		return null;

	}

	/**
	 * 根据用户id 获取该用户的组织机构
	 * 
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysOrganization> querySysOrgsForSSO(int userId) {
		// 此处需打开Session
		Session s = hibernateTemplate.getSessionFactory().openSession();
		// 使用SQL查找机构ID
		SQLQuery sqlQuery = s
				.createSQLQuery("select sysOrganizations_id from KQ_Operator_KQ_SYSORGANIZATION where sysUsers_OpID  = :userId");
		sqlQuery.setParameter("userId", userId);
		List<String> orgIds = sqlQuery.list();
		// 使用HQL返回机构列表
		Query query = s
				.createQuery("from SysOrganization so where so.id in (:sId) ");
		query.setParameterList("sId", orgIds);
		return query.list();
	}

	/**
	 * 通过用户名查找用户
	 * 
	 * @param userName
	 * @return
	 * @throws FrameworkException
	 */
	@SuppressWarnings("unchecked")
	public SysUser findByUserName(String userName) throws FrameworkException {
		Criteria criteria = hibernateTemplate.getSessionFactory()
				.getCurrentSession().createCriteria(SysUser.class);
		if (!BaseUtil.isEmpty(userName)) {
			criteria.add(Restrictions.eq("userName", userName));
		}
		criteria.add(Restrictions.eq("disabled", true));
		List<SysUser> sysUsers = criteria.list();
		if (!BaseUtil.isEmpty(sysUsers) && sysUsers.size() > 0)
			return sysUsers.get(0);
		return null;
	}
}
